﻿using FRMS.DAL;
using FRMS.Model;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Xml;

namespace FRMS.BUS
{
    public class AttendanceBUS
    {
        public ResponseMessage ImportDate(string path, DataTable dtExcel, int year)
        {
            ResponseMessage message = new ResponseMessage();
            try
            {
                string filepath = path + "/AttendanceDate_" + year.ToString() + ".xml";
                // 判断文件是否存在，不存在则创建，否则清空里面的内容
                if (!System.IO.File.Exists(filepath))
                {
                    FileStream fs1 = new FileStream(filepath, FileMode.Create, FileAccess.Write);//创建写入文件 
                    fs1.Close();
                }
                else
                {
                    System.IO.File.WriteAllText(filepath, string.Empty);
                }

                XmlDocument xmlDoc = new XmlDocument();
                //创建类型声明节点  
                XmlNode node = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", "");
                xmlDoc.AppendChild(node);
                //创建Xml根节点  
                XmlNode root = xmlDoc.CreateElement("DateYear");
                xmlDoc.AppendChild(root);

                int count = dtExcel.Rows.Count;
                for (int i = 1; i < count; i++)
                {
                    if (!string.IsNullOrEmpty(dtExcel.Rows[i][0].ToString().Trim()))
                    {
                        DateTime dateTime = Convert.ToDateTime(dtExcel.Rows[i][0].ToString().Trim());

                        CreateNodeDate(xmlDoc, root, "DateDay", dateTime.ToString("yyyy-MM-dd"));

                    }
                }
                xmlDoc.Save(filepath);
            }
            catch (Exception e)
            {
                DataClearBUS dataClearBUS = new DataClearBUS();
                ErrorLog errorLog = new ErrorLog();
                errorLog.ErrorMsg = e.ToString();
                errorLog.Operation = "导入日期";
                dataClearBUS.AddErrorLog(errorLog);
                message.errcode = 1;
                message.errmsg = "导入日期异常";
            }
            return message;
        }

        public void CreateNodeDate(XmlDocument xmlDoc, XmlNode parentNode, string name, string value)
        {
            //创建对应Xml节点元素
            XmlNode node = xmlDoc.CreateNode(XmlNodeType.Element, name, null);
            node.InnerText = value;
            parentNode.AppendChild(node);
        }

        public ResponseMessage QueryRecord(CheckInOutInfo checkInOutInfo)
        {
            ResponseMessage response = new ResponseMessage();
            try
            {
                AttendanceDAL attendanceDAL = new AttendanceDAL();
                List<AttendanceInfo> list = attendanceDAL.QueryCheckInOut(checkInOutInfo);
                response.data = list;
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.ToString());
                response.errcode = 1;
                response.data = null;
            }
            return response;
        }

        public ResponseMessage ImportOwner(string path, DataTable dtExcel)
        {
            ResponseMessage message = new ResponseMessage();

            int count = dtExcel.Rows.Count;
            AttendanceDAL attendanceDAL = new AttendanceDAL();
            for (int i = 1; i < count; i++)
            {
                if (!string.IsNullOrEmpty(dtExcel.Rows[i][1].ToString().Trim()))
                {
                    string EmployeeNo = dtExcel.Rows[i][1].ToString().Trim();
                    string name = dtExcel.Rows[i][2].ToString().Trim().Replace(" ", "");
                    string depart = dtExcel.Rows[i][3].ToString().Trim().Replace(" ", "");
                    int checktype = Convert.ToInt32(dtExcel.Rows[i][5].ToString().Trim());
                    int datetype = Convert.ToInt32(dtExcel.Rows[i][6].ToString().Trim());
                    OwnerBUS ownerBUS = new OwnerBUS();
                    string UUID = ownerBUS.GetUUID(1);
                    attendanceDAL.AddOwner(name, UUID, EmployeeNo, depart, checktype, datetype);
                }

            }
            return message;
        }

        public ResponseMessage ExportRecord(string path, string starttime, string endtime, int projectid)
        {
            ResponseMessage message = new ResponseMessage();
            try
            {
                AttendanceDAL attendanceDAL = new AttendanceDAL();
                //查询所有部门
                List<BuildInfo> list = new List<BuildInfo>();
                list = attendanceDAL.QueryAllDepart(projectid);

                DateTime date_start = Convert.ToDateTime(starttime).Date;
                DateTime date_end = Convert.ToDateTime(endtime).Date;

                //查询所有考勤记录
                List<AttendanceInfo> list_record = attendanceDAL.QueryAllAttendanceRecord(date_start, date_end);

                //查询所有漏打卡记录
                List<MissingClockIn> missingClockIns_list = new List<MissingClockIn>();
                missingClockIns_list = attendanceDAL.QueryAllMissingClockIn(date_start, date_end);

                //查询所有请假记录
                List<LeaveInfo> leaveInfos = new List<LeaveInfo>();
                leaveInfos = attendanceDAL.QueryAllLeave(date_start, date_end);

                //查询所有公出记录
                List<PublicRelease> publicReleases = new List<PublicRelease>();
                publicReleases = attendanceDAL.QueryAllPublicRelease(date_start, date_end);

                TimeSpan timeSpan = new TimeSpan(date_end.Ticks - date_start.Ticks);
                int days = timeSpan.Days + 1;

                List<string> list_date = QueryDate(path, date_start.Year);

                DeleteLog(path);
                string sFileName = "考勤记录" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                FileInfo file = new FileInfo(Path.Combine(path + "/wwwroot", sFileName));
                file.Delete();

                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                using (ExcelPackage package = new ExcelPackage(file))
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        // 添加worksheet
                        //查询该部门下的所有人员
                        List<OwnerInfo> list_owner = attendanceDAL.QueryOwnerByBuildID(list[i].ID);
                        if (!string.IsNullOrEmpty(list[i].BuildingName))
                        {
                            if (list_owner != null && list_owner.Count > 0)
                            {
                                // 选择工作表
                                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(list[i].BuildingName);
                                worksheet.Cells[1, 2].Value = "日期";
                                worksheet.Cells[2, 1].Value = "考勤号码";
                                worksheet.Cells[2, 2].Value = "姓名";
                                for (int m = 0; m < list_owner.Count; m++)
                                {
                                    worksheet.Cells[3 + m, 1].Value = list_owner[m].EmployeeNo;
                                    worksheet.Cells[3 + m, 2].Value = list_owner[m].OwnerName;
                                }
                                for (int k = 0; k < days; k++)
                                {
                                    DateTime dt = date_start.AddDays(k);
                                    if (!list_date.Contains(dt.ToString("yyyy-MM-dd")))
                                    {
                                        //添加头
                                        worksheet.Cells[1, 3 + 2 * k].Value = dt.ToString("MM-dd");
                                        worksheet.Cells[2, 3 + 2 * k].Value = "上午";
                                        worksheet.Cells[2, 4 + 2 * k].Value = "下午";
                                        var cellRange = worksheet.Cells[1, 3 + 2 * k, 1, 4 + 2 * k];
                                        cellRange.Merge = true;
                                        for (int n = 0; n < list_owner.Count; n++)
                                        {
                                            List<MissingClockIn> missingClockIns = missingClockIns_list.FindAll(delegate (MissingClockIn e) { return Convert.ToDateTime(e.date) == dt && e.data_121 == list_owner[n].OwnerName; });
                                            List<PublicRelease> publics = publicReleases.FindAll(delegate (PublicRelease e) { return Convert.ToDateTime(e.data_6).Date <= dt && Convert.ToDateTime(e.data_7).Date >= dt && e.data_13.Contains(list_owner[n].OwnerName); });
                                            List<LeaveInfo> leaves = leaveInfos.FindAll(delegate (LeaveInfo e) { return Convert.ToDateTime(e.data_80).Date <= dt && Convert.ToDateTime(e.data_81).Date >= dt && e.data_95.Contains(list_owner[n].OwnerName); });
                                            CellValue cellValue = Statistics(list_owner[n].EmployeeNo, dt, list_owner[n].CheckType, list_owner[n].DateType, list_record, missingClockIns, publics, leaves);
                                            worksheet.Cells[3 + n, 3 + 2 * k].Value = cellValue.morning;
                                            // 获取单元格
                                            //if (cellValue.morning != "全")
                                            if (cellValue.morning.Contains("上午") || cellValue.morning.Contains("无"))
                                            {
                                                worksheet.Cells[3 + n, 3 + 2 * k].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                                worksheet.Cells[3 + n, 3 + 2 * k].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0xccccfff));
                                            }
                                            else if (cellValue.morning.Contains("迟到"))
                                            {
                                                worksheet.Cells[3 + n, 3 + 2 * k].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                                worksheet.Cells[3 + n, 3 + 2 * k].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0xff3399));
                                            }
                                            else if (cellValue.morning != "全")
                                            {
                                                worksheet.Cells[3 + n, 3 + 2 * k].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                                worksheet.Cells[3 + n, 3 + 2 * k].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0xffff00));
                                            }

                                            worksheet.Cells[3 + n, 3 + 2 * k + 1].Value = cellValue.afternoon;
                                            //if (cellValue.afternoon != "全")
                                            if (cellValue.afternoon.Contains("下午") || cellValue.afternoon.Contains("无"))
                                            {
                                                worksheet.Cells[3 + n, 3 + 2 * k + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                                worksheet.Cells[3 + n, 3 + 2 * k + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0xccccfff));
                                            }
                                            else if (cellValue.afternoon.Contains("早退"))
                                            {
                                                worksheet.Cells[3 + n, 3 + 2 * k + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                                worksheet.Cells[3 + n, 3 + 2 * k + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0xff3399));
                                            }
                                            else if (cellValue.afternoon != "全")
                                            {
                                                worksheet.Cells[3 + n, 3 + 2 * k + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                                worksheet.Cells[3 + n, 3 + 2 * k + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0xffff00));
                                            }
                                        }
                                    }
                                }
                                worksheet.Cells.AutoFitColumns();
                                worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                            }
                        }
                    }
                    package.Save();
                }
                message.errcode = 0;
                message.errmsg = "";
                message.data = "/" + sFileName;
            }
            catch (Exception ex)
            {
                message.errcode = 1;
                message.errmsg = "导出失败";
                LogHelper.Log(ex.ToString());
            }
            return message;
        }

        public void DeleteLog(string path)
        {
            try
            {
                string logPath = path + "/wwwroot";
                if (!Directory.Exists(logPath))
                {
                    return;
                }
                DirectoryInfo folder_base = new DirectoryInfo(logPath);
                FileInfo[] files_base = folder_base.GetFiles("*.xlsx");  //获取.txt文件
                foreach (var file_txt in files_base)
                {
                    try
                    {
                        File.Delete(file_txt.FullName);
                    }
                    catch
                    {
                        continue;
                    }
                }
            }
            catch (Exception e)
            {
                LogHelper.Log(e.ToString());
            }
        }

        public CellValue Statistics(string EmployeeNo, DateTime dateTime, int checktype, int datetype, List<AttendanceInfo> all_list, List<MissingClockIn> missingClocks, List<PublicRelease> publicReleases, List<LeaveInfo> leaves)
        {
            CellValue cellValue = new CellValue();
            try
            {
                AttendanceDAL attendanceDAL = new AttendanceDAL();
                int month = dateTime.Month;
                DateTime dateTime_end = dateTime.AddDays(1);

                //上午上班时间
                string date_1 = dateTime.ToString("yyyy-MM-dd") + " 08:17";
                //上午下班时间
                string date_2 = string.Empty;
                if (datetype == 1)
                {
                    date_2 = dateTime.ToString("yyyy-MM-dd") + " 11:30";
                }
                else
                {
                    date_2 = dateTime.ToString("yyyy-MM-dd") + " 11:20";
                }

                //下午上班时间
                string date_3 = string.Empty;
                //下午下班时间
                string date_4 = string.Empty;
                if (month >= 5 && month <= 9)
                {
                    //夏令时上班时间
                    if (datetype == 1)
                    {
                        //办公室
                        date_3 = dateTime.ToString("yyyy-MM-dd") + " 12:32";
                    }
                    else
                    {
                        //产线
                        date_3 = dateTime.ToString("yyyy-MM-dd") + " 12:22";
                    }
                    //夏令时下班时间
                    date_4 = dateTime.ToString("yyyy-MM-dd") + " 17:15";
                }
                else
                {
                    //冬令时上班时间
                    if (datetype == 1)
                    {
                        //办公室
                        date_3 = dateTime.ToString("yyyy-MM-dd") + " 12:17";
                    }
                    else
                    {
                        //产线
                        date_3 = dateTime.ToString("yyyy-MM-dd") + " 12:07";
                    }
                    //冬令时下班时间
                    date_4 = dateTime.ToString("yyyy-MM-dd") + " 17:00";
                }

                DateTime dateTime1 = Convert.ToDateTime(date_1);
                DateTime dateTime2 = Convert.ToDateTime(date_2);
                DateTime dateTime3 = Convert.ToDateTime(date_3);
                DateTime dateTime4 = Convert.ToDateTime(date_4);

                List<AttendanceInfo> list = all_list.FindAll(delegate (AttendanceInfo e) { return Convert.ToDateTime(e.CHECKTIME) >= dateTime && Convert.ToDateTime(e.CHECKTIME) < dateTime_end && e.BADGENUMBER == EmployeeNo; });
                if (list != null && list.Count > 0)
                {
                    int sw_value = 0;
                    int xw_value = 0;
                    string sw_value_1 = string.Empty;
                    string sw_value_2 = string.Empty;
                    string xw_value_1 = string.Empty;
                    string xw_value_2 = string.Empty;

                    if (checktype == 1) //中午免打卡
                    {
                        //早上上班打卡
                        List<AttendanceInfo> query_list = new List<AttendanceInfo>();

                        query_list = list.FindAll(delegate (AttendanceInfo e) { return Convert.ToDateTime(e.CHECKTIME) <= dateTime1; });
                        if (query_list.Count > 0)
                        {
                            sw_value++;
                        }
                        //下午下班打卡
                        query_list = list.FindAll(delegate (AttendanceInfo e) { return Convert.ToDateTime(e.CHECKTIME) >= dateTime4; });

                        if (query_list.Count > 0)
                        {
                            xw_value++;
                        }
                        if (sw_value == 0)
                        {
                            cellValue.morning = "无";
                        }
                        else if (sw_value == 1)
                        {
                            cellValue.morning = "全";
                        }
                        if (xw_value == 0)
                        {
                            cellValue.afternoon = "无";
                        }
                        else if (xw_value == 1)
                        {
                            cellValue.afternoon = "全";
                        }
                    }
                    else if (checktype == 2)//四次卡
                    {
                        //早上上班打卡
                        List<AttendanceInfo> query_list = new List<AttendanceInfo>();
                        query_list = list.FindAll(delegate (AttendanceInfo e) { return Convert.ToDateTime(e.CHECKTIME) <= dateTime1; });

                        if (query_list.Count == 0)
                        {
                            sw_value_1 = "上午1";
                        }

                        //中午打卡
                        query_list = list.FindAll(delegate (AttendanceInfo e) { return Convert.ToDateTime(e.CHECKTIME) >= dateTime2 && Convert.ToDateTime(e.CHECKTIME) <= dateTime3; });
                        if (query_list.Count > 1)
                        {
                            string starttime = query_list[0].CHECKTIME;
                            string endtime = query_list[query_list.Count - 1].CHECKTIME;

                            DateTime date_start = Convert.ToDateTime(starttime);
                            DateTime date_end = Convert.ToDateTime(endtime);
                            TimeSpan timeSpan = new TimeSpan(date_end.Ticks - date_start.Ticks);
                            if (!(timeSpan.Minutes >= 9 || timeSpan.Hours > 0))
                            {
                                xw_value_1 = "下午1";
                            }
                        }
                        else if (query_list.Count == 1)
                        {
                            DateTime date_start = dateTime2;
                            DateTime date_end = dateTime3;
                            DateTime checktime = Convert.ToDateTime(query_list[0].CHECKTIME);
                            TimeSpan timeSpan_1 = new TimeSpan(date_end.Ticks - checktime.Ticks);
                            TimeSpan timeSpan_2 = new TimeSpan(checktime.Ticks - date_start.Ticks);
                            if (timeSpan_1.TotalSeconds >= timeSpan_2.TotalSeconds)
                            {
                                xw_value_1 = "下午1";
                            }
                            else
                            {
                                sw_value_2 = "上午2";
                            }

                            sw_value++;
                        }
                        else
                        {
                            xw_value_1 = "下午1";
                            sw_value_2 = "上午2";
                        }

                        //下午下班打卡
                        query_list = list.FindAll(delegate (AttendanceInfo e) { return Convert.ToDateTime(e.CHECKTIME) >= dateTime4; });

                        if (query_list.Count == 0)
                        {
                            xw_value_2 = "下午2";
                        }


                        if (string.IsNullOrEmpty(sw_value_1) && string.IsNullOrEmpty(sw_value_2))
                        {
                            cellValue.morning = "全";
                        }
                        else if (!string.IsNullOrEmpty(sw_value_1) && !string.IsNullOrEmpty(sw_value_2))
                        {
                            cellValue.morning = "无";
                        }
                        else
                        {
                            cellValue.morning = sw_value_1 + sw_value_2;
                        }
                        if (string.IsNullOrEmpty(xw_value_1) && string.IsNullOrEmpty(xw_value_2))
                        {
                            cellValue.afternoon = "全";
                        }
                        else if (!string.IsNullOrEmpty(xw_value_1) && !string.IsNullOrEmpty(xw_value_2))
                        {
                            cellValue.afternoon = "无";
                        }
                        else
                        {
                            cellValue.afternoon = xw_value_1 + xw_value_2;
                        }
                    }
                    else if (checktype == 3)//一次卡
                    {
                        if (list.Count > 0)
                        {
                            cellValue.afternoon = "全";
                            cellValue.morning = "全";
                        }
                        else
                        {
                            cellValue.afternoon = "无";
                            cellValue.morning = "无";
                        }
                    }
                }
                else
                {
                    cellValue.afternoon = "无";
                    cellValue.morning = "无";
                }

                //漏打卡
                if (missingClocks.Count > 0)
                {
                    //2次卡
                    if (checktype == 1)
                    {
                        foreach (var item in missingClocks)
                        {
                            if (item.data_98 == "on" || item.data_102 == "on" || item.data_99 == "on" || item.data_103 == "on")
                            {
                                if (item.data_118 == "因公")
                                {
                                    cellValue.morning = "全";
                                }
                                else
                                {
                                    cellValue.morning = "漏";
                                }
                            }
                            if (item.data_100 == "on" || item.data_104 == "on" || item.data_101 == "on" || item.data_105 == "on")
                            {
                                if (item.data_118 == "因公")
                                {
                                    cellValue.morning = "全";
                                }
                                else
                                {
                                    cellValue.morning = "漏";
                                }

                            }
                        }
                    }
                    else if (checktype == 2)//四次卡
                    {
                        if (cellValue.morning == "无")
                        {
                            cellValue.morning = "上午1上午2";
                        }
                        if (cellValue.afternoon == "无")
                        {
                            cellValue.afternoon = "下午1下午2";
                        }
                        foreach (var item in missingClocks)
                        {
                            if (item.data_98 == "on" || item.data_102 == "on")
                            {
                                if (item.data_118 == "因公")
                                {
                                    cellValue.morning = cellValue.morning.Replace("上午1", "");
                                }
                                else
                                {
                                    cellValue.morning = cellValue.morning.Replace("上午1", "漏");
                                }

                            }
                            if (item.data_99 == "on" || item.data_103 == "on")
                            {
                                if (item.data_118 == "因公")
                                {
                                    cellValue.morning = cellValue.morning.Replace("上午2", "");
                                }
                                else
                                {
                                    cellValue.morning = cellValue.morning.Replace("上午2", "漏");
                                }
                            }
                            if (item.data_100 == "on" || item.data_104 == "on")
                            {
                                if (item.data_118 == "因公")
                                {
                                    cellValue.afternoon = cellValue.afternoon.Replace("下午1", "");
                                }
                                else
                                {
                                    cellValue.afternoon = cellValue.afternoon.Replace("下午1", "漏");
                                }
                            }
                            if (item.data_101 == "on" || item.data_105 == "on")
                            {
                                if (item.data_118 == "因公")
                                {
                                    cellValue.afternoon = cellValue.afternoon.Replace("下午2", "");
                                }
                                else
                                {
                                    cellValue.afternoon = cellValue.afternoon.Replace("下午2", "漏");
                                }
                            }
                        }

                        if (string.IsNullOrEmpty(cellValue.morning))
                        {
                            cellValue.morning = "全";
                        }
                        if (string.IsNullOrEmpty(cellValue.afternoon))
                        {
                            cellValue.afternoon = "全";
                        }
                    }
                    else if (checktype == 3)//一次卡
                    {
                        foreach (var item in missingClocks)
                        {
                            if (item.data_98 == "on" || item.data_102 == "on" || item.data_99 == "on" || item.data_103 == "on" || item.data_100 == "on" || item.data_104 == "on" || item.data_101 == "on" || item.data_105 == "on")
                            {
                                if (item.data_118 == "因公")
                                {
                                    cellValue.morning = "全";
                                    cellValue.afternoon = "全";
                                }
                                else
                                {
                                    cellValue.morning = "漏";
                                    cellValue.afternoon = "漏";
                                }
                            }
                        }
                    }
                }

                //公出
                if (publicReleases.Count > 0)
                {
                    if (checktype == 1)//
                    {
                        foreach (var item in publicReleases)
                        {
                            DateTime date_start = Convert.ToDateTime(item.data_6);
                            DateTime date_end = Convert.ToDateTime(item.data_7);

                            if (date_start >= dateTime1)
                            {
                                cellValue.morning = "公出";
                            }
                            if (date_end >= dateTime4)
                            {
                                cellValue.afternoon = "公出";
                            }
                        }
                    }
                    else if (checktype == 2)
                    {
                        if (cellValue.morning == "无")
                        {
                            cellValue.morning = "上午1上午2";
                        }
                        if (cellValue.afternoon == "无")
                        {
                            cellValue.afternoon = "下午1下午2";
                        }
                        foreach (var item in publicReleases)
                        {
                            DateTime date_start = Convert.ToDateTime(item.data_6);
                            DateTime date_end = Convert.ToDateTime(item.data_7);

                            if (date_start <= dateTime1)
                            {
                                cellValue.morning = cellValue.morning.Replace("上午1", "公出");
                            }
                            if (date_end >= dateTime2)
                            {
                                cellValue.morning = cellValue.morning.Replace("上午2", "公出");
                            }
                            if (date_end >= dateTime3)
                            {
                                cellValue.afternoon = cellValue.afternoon.Replace("下午1", "公出");
                            }
                            if (date_end >= dateTime4)
                            {
                                cellValue.afternoon = cellValue.afternoon.Replace("下午2", "公出");
                            }
                        }
                        if (cellValue.morning == "公出公出")
                        {
                            cellValue.morning = "公出";
                        }
                        if (cellValue.afternoon == "公出公出")
                        {
                            cellValue.afternoon = "公出";
                        }

                    }
                    else if (checktype == 3)
                    {
                        cellValue.morning = "公出";
                        cellValue.afternoon = "公出";
                    }
                }

                //请假
                if (leaves.Count > 0)
                {
                    if (checktype == 1)//2次卡
                    {
                        foreach (var item in leaves)
                        {
                            DateTime date_start = Convert.ToDateTime(item.data_80);
                            DateTime date_end = Convert.ToDateTime(item.data_81);
                            if (date_start <= dateTime1)
                            {
                                cellValue.morning = item.data_86;
                            }
                            if (date_end >= dateTime4)
                            {
                                cellValue.afternoon = item.data_86;
                            }
                        }
                    }
                    else if (checktype == 2)
                    {
                        if (cellValue.morning == "无")
                        {
                            cellValue.morning = "上午1上午2";
                        }
                        if (cellValue.afternoon == "无")
                        {
                            cellValue.afternoon = "下午1下午2";
                        }

                        foreach (var item in leaves)
                        {
                            DateTime date_start = Convert.ToDateTime(item.data_80);
                            DateTime date_end = Convert.ToDateTime(item.data_81);

                            if (date_start <= dateTime1)
                            {
                                if (cellValue.morning.Contains(item.data_86))
                                {
                                    cellValue.morning = cellValue.morning.Replace("上午1", "");
                                }
                                else
                                {
                                    if (cellValue.morning == "全")
                                    {
                                        cellValue.morning = item.data_86;
                                    }
                                    else
                                    {
                                        cellValue.morning = cellValue.morning.Replace("上午1", item.data_86);
                                    }
                                }
                            }
                            if (date_end >= dateTime2)
                            {
                                if (cellValue.morning.Contains(item.data_86))
                                {
                                    cellValue.morning = cellValue.morning.Replace("上午2", "");
                                    cellValue.afternoon = cellValue.afternoon.Replace("下午1", "");
                                    if (string.IsNullOrEmpty(cellValue.afternoon))
                                    {
                                        cellValue.afternoon = "全";
                                    }
                                }
                                else
                                {
                                    cellValue.morning = cellValue.morning.Replace("上午2", item.data_86);
                                }
                            }

                            if (date_end >= dateTime3)
                            {
                                if (cellValue.afternoon.Contains(item.data_86))
                                {
                                    cellValue.afternoon = cellValue.afternoon.Replace("下午1", "");
                                }
                                else
                                {
                                    if (cellValue.afternoon == "全")
                                    {
                                        cellValue.afternoon = item.data_86;
                                    }
                                    else
                                    {
                                        cellValue.afternoon = cellValue.afternoon.Replace("下午1", item.data_86);
                                    }
                                }
                            }
                            if (date_end >= dateTime4)
                            {
                                if (cellValue.afternoon.Contains(item.data_86))
                                {
                                    cellValue.afternoon = cellValue.afternoon.Replace("下午2", "");
                                }
                                else
                                {
                                    if (cellValue.afternoon == "全")
                                    {
                                        cellValue.afternoon = item.data_86;
                                    }
                                    else
                                    {
                                        cellValue.afternoon = cellValue.afternoon.Replace("下午2", item.data_86);
                                    }
                                }
                            }

                            if (date_start > dateTime1 && date_end < dateTime2)
                            {
                                if (cellValue.morning == "全")
                                {
                                    cellValue.morning = item.data_86;
                                }
                            }

                            if (date_start > dateTime3 && date_end < dateTime4)
                            {
                                if (cellValue.afternoon == "全")
                                {
                                    cellValue.afternoon = item.data_86;
                                }
                            }
                        }

                        if (cellValue.morning == "上午1上午2")
                        {
                            cellValue.morning = "无";
                        }
                        if (cellValue.afternoon == "下午1下午2")
                        {
                            cellValue.afternoon = "无";
                        }
                    }
                    else if (checktype == 3)
                    {
                        cellValue.morning = leaves[0].data_86;
                        cellValue.afternoon = leaves[0].data_86;
                    }
                }

                if ((cellValue.morning == "上午1" && checktype == 2) || (cellValue.morning == "无" && checktype == 1))
                {
                    if (list != null)
                    {
                        List<AttendanceInfo> attendances = list.FindAll(delegate (AttendanceInfo e) { return Convert.ToDateTime(e.CHECKTIME) >= dateTime1 && Convert.ToDateTime(e.CHECKTIME) <= dateTime1.AddMinutes(28); });
                        if (attendances.Count > 0)
                        {
                            DateTime checktime = Convert.ToDateTime(attendances[0].CHECKTIME);
                            TimeSpan timeSpan_1 = new TimeSpan(checktime.Ticks - dateTime1.AddMinutes(-2).Ticks);
                            cellValue.morning = "迟到" + Convert.ToInt32(timeSpan_1.TotalMinutes) + "分钟";
                        }
                    }
                }
                if ((cellValue.afternoon == "下午2" && checktype == 2) || (cellValue.afternoon == "无" && checktype == 1))
                {
                    if (list != null)
                    {
                        List<AttendanceInfo> attendances = list.FindAll(delegate (AttendanceInfo e) { return Convert.ToDateTime(e.CHECKTIME) <= dateTime4 && Convert.ToDateTime(e.CHECKTIME) >= dateTime4.AddMinutes(-30); });
                        if (attendances.Count > 0)
                        {
                            DateTime checktime = Convert.ToDateTime(attendances[attendances.Count - 1].CHECKTIME);
                            TimeSpan timeSpan_1 = new TimeSpan(dateTime4.Ticks - checktime.Ticks);
                            cellValue.afternoon = "早退" + Convert.ToInt32(timeSpan_1.TotalMinutes) + "分钟";
                        }
                    }
                }
            }
            catch
            {
            }
            return cellValue;
        }

        public List<string> QueryDate(string path, int year)
        {
            List<string> list = new List<string>();
            string filepath = path + "/AttendanceDate_" + year.ToString() + ".xml";
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(filepath);
            XmlNode xmlNode = xmlDoc.SelectSingleNode("DateYear");//父节点
            var conStr = String.Empty;
            var returnVal = string.Empty;
            var returnKey = string.Empty;

            foreach (XmlNode xmlN in xmlNode)
            {
                //遍历子节点
                XmlElement xl = (XmlElement)xmlN;
                list.Add(xl.InnerText);
            }
            return list;
        }

        public ResponseMessage QueryDepart()
        {
            ResponseMessage response = new ResponseMessage();
            try
            {
                AttendanceDAL attendanceDAL = new AttendanceDAL();
                List<Department> list = attendanceDAL.QueryKQDepart();
                response.data = list;
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.ToString());
                response.errcode = 1;
                response.data = null;
            }
            return response;
        }

        public ResponseMessage ExportWorkOverTime(string path, string starttime, string endtime)
        {
            ResponseMessage message = new ResponseMessage();
            try
            {
                AttendanceDAL attendanceDAL = new AttendanceDAL();
                //查询所有人员 按照部门排序
                List<OwnerInfo> list_owner = attendanceDAL.QueryOwner();

                DateTime date_start = Convert.ToDateTime(starttime);
                DateTime date_end = Convert.ToDateTime(endtime);

                //查询所有加班记录
                List<WorkOvertime> list_record = attendanceDAL.QueryWorkOverTime(date_start, date_end);

                List<string> list_date = QueryDate(path, date_start.Year);

                TimeSpan timeSpan = new TimeSpan(date_end.Ticks - date_start.Ticks);
                int days = timeSpan.Days + 1;

                DeleteLog(path);
                string sFileName = "加班统计" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                FileInfo file = new FileInfo(Path.Combine(path + "/wwwroot", sFileName));
                file.Delete();

                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                using (ExcelPackage package = new ExcelPackage(file))
                {
                    // 添加worksheet
                    // 选择工作表
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("加班统计");
                    worksheet.Cells[1, 3].Value = "日期";
                    worksheet.Cells[2, 1].Value = "部门";
                    worksheet.Cells[2, 2].Value = "工号";
                    worksheet.Cells[2, 3].Value = "姓名";
                    worksheet.Cells[1, 4 + days].Value = "平时加班";
                    worksheet.Cells[1, 5 + days].Value = "周末加班";
                    worksheet.Cells[1, 6 + days].Value = "调休";
                    //查询该部门下的所有人员

                    if (list_owner != null && list_owner.Count > 0)
                    {
                        for (int m = 0; m < list_owner.Count; m++)
                        {
                            worksheet.Cells[3 + m, 2].Value = list_owner[m].EmployeeNo;
                            worksheet.Cells[3 + m, 3].Value = list_owner[m].OwnerName;
                            worksheet.Cells[3 + m, 1].Value = list_owner[m].BuildingName;
                        }

                        for (int k = 0; k < days; k++)
                        {
                            DateTime dt = date_start.AddDays(k);

                            //添加头
                            worksheet.Cells[1, 4 + k].Value = dt.ToString("yyyy-MM-dd");
                            if (list_date.Contains(dt.ToString("yyyy-MM-dd")))
                            {
                                worksheet.Cells[1, 4 + k].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                worksheet.Cells[1, 4 + k].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0xFF0000));
                            }

                            List<WorkOvertime> list = new List<WorkOvertime>();

                            for (int n = 0; n < list_owner.Count; n++)
                            {
                                if (list_record != null)
                                {
                                    list = list_record.FindAll(delegate (WorkOvertime e) { return Convert.ToDateTime(e.data_14).Date == dt.Date && (e.data_13.Contains(list_owner[n].OwnerName)); });
                                    if (list.Count > 0)
                                    {
                                        Decimal overtime = 0;
                                        if (list.Count > 0)
                                        {
                                            for (int m = 0; m < list.Count; m++)
                                            {
                                                string[] array = list[m].data_13.Replace("\n", "").Replace("\r", "").Split('`');
                                                int index = Array.FindIndex(array, item => item == list_owner[n].OwnerName);
                                                if (index > -1)
                                                {
                                                    decimal num = 0;
                                                    try
                                                    {
                                                        num = Convert.ToDecimal(array[index + 1]);
                                                    }
                                                    catch
                                                    {

                                                    }
                                                    overtime += num;
                                                }
                                                else
                                                {

                                                }

                                            }
                                        }

                                        worksheet.Cells[3 + n, 4 + k].Value = overtime;

                                        if (list_date.Contains(dt.ToString("yyyy-MM-dd")))
                                        {
                                            decimal data = Convert.ToDecimal(worksheet.Cells[3 + n, 5 + days].Value);
                                            worksheet.Cells[3 + n, 5 + days].Value = overtime + data;
                                        }
                                        else
                                        {
                                            decimal data = Convert.ToDecimal(worksheet.Cells[3 + n, 4 + days].Value);
                                            worksheet.Cells[3 + n, 4 + days].Value = overtime + data;
                                        }

                                        worksheet.Cells[3 + n, 6 + days].Value = Convert.ToDecimal(worksheet.Cells[3 + n, 4 + days].Value) + Convert.ToDecimal(worksheet.Cells[3 + n, 5 + days].Value);

                                        if (list.Count > 0)
                                        {
                                            if (list[0].data_47 == "on")
                                            {
                                                worksheet.Cells[3 + n, 4 + k].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                                worksheet.Cells[3 + n, 4 + k].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0x99ffff));
                                            }
                                        }
                                    }
                                    else
                                    {
                                        worksheet.Cells[3 + n, 4 + k].Value = "";
                                    }
                                }
                                else
                                {
                                    worksheet.Cells[3 + n, 4 + k].Value = "";
                                }
                            }
                        }


                        worksheet.Cells.AutoFitColumns();
                        worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    }
                    package.Save();
                }
                message.errcode = 0;
                message.errmsg = "";
                message.data = "/" + sFileName;
            }
            catch (Exception ex)
            {
                message.errcode = 1;
                message.errmsg = "导出失败";
                LogHelper.Log(ex.ToString());
            }
            return message;
        }
    }

}
